package com.pur.zsms.ordercustommanage.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Query;

import net.sf.json.JSONObject;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.common.util.CipherUtil;
import com.pur.zsms.common.vo.CommonType;
import com.pur.zsms.foodmanage.food.po.FoodMaterPo;
import com.pur.zsms.foodmanage.food.po.FoodPo;
import com.pur.zsms.matermanage.mater.dao.IMaterDao;
import com.pur.zsms.matermanage.mater.po.MaterLevelPo;
import com.pur.zsms.matermanage.mater.po.MaterPo;
import com.pur.zsms.matermanage.mater.vo.MaterVo;
import com.pur.zsms.ordercustommanage.dao.ISelectFoodDao;
import com.pur.zsms.ordercustommanage.po.OrderCustFoodPo;
import com.pur.zsms.ordercustommanage.po.OrderCustPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierMaterNeedPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierMaterPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierMaterRealPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierPo;
import com.pur.zsms.ordercustommanage.vo.OrderCustVo;
import com.pur.zsms.ordercustommanage.vo.OrderStateVo;
import com.pur.zsms.ordercustommanage.vo.OrderSupplierMaterRealVo;
import com.pur.zsms.pmsnmanage.menu.po.MenuPo;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.pmsnmanage.user.po.UserRolePo;
import com.pur.zsms.pmsnmanage.user.vo.UserVo;
import com.pur.zsms.supplier.po.ShopSupplierTypePo;
import com.pur.zsms.supplier.po.SupplierQualificationPo;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;

@Repository("selectFoodDao")
@Transactional
public class SelectFoodDaoImpl extends GenericDao implements ISelectFoodDao {
	@Override
	public int auto() throws GenericException {
		List quert = this.entityManager.createNativeQuery("SELECT 1").getResultList();
		return Integer.valueOf(quert.get(0).toString());
	}

	public void addSelectFood(OrderCustPo po) throws GenericException {
		this.persist(po);
	}

	public void updateSelectFood(OrderCustPo po) throws GenericException {
		this.merge(po);
	}

	public void addOrderSupplierMater(OrderSupplierMaterPo po) throws GenericException {
		this.persist(po);
	}

	public void addOrderSupplierMaterReal(OrderSupplierMaterRealPo po) throws GenericException {
		this.persist(po);
	}

	public void addOrderSupplierMaterNeed(OrderSupplierMaterNeedPo po) throws GenericException {
		this.persist(po);
	}

	public void updateOrderSupplierMater(OrderSupplierMaterPo po) throws GenericException {
		this.merge(po);
	}

	public void updateOrderSupplierMaterReal(OrderSupplierMaterRealPo po) throws GenericException {
		this.merge(po);
	}

	public void addOrderFood(OrderCustFoodPo po) throws GenericException {
		this.persist(po);
	}

	public void updateOrderFood(OrderCustFoodPo po) throws GenericException {
		this.merge(po);
	}

	@Override
	public PaginationSupport listMyOrder(PageController pageController, OrderCustVo orderCustVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT t.ord_id ordId,t.ord_cust_id ordCustId,t.ord_state ordState,t.ord_date ordDate,t.ord_day ordDay,t.ord_value ordValue,t.review_remark,b.shop_name,t.submit_ord_time_last,t.buy_self_flag  FROM  t_order_custom t,t_shop b ");
		ql.append("  where b.shop_id = t.ord_cust_id and t.ord_cust_id = '" + orderCustVo.getOrdCustId());
		ql.append("'");
		if (orderCustVo.getOrdState() != 0) {
			ql.append(" and t.ord_state = '" + orderCustVo.getOrdState() + "'");
		}
		if (orderCustVo.getOrdDate() != null && !orderCustVo.getOrdDate().equals("")) {
			ql.append(" and t.ord_date = '" + orderCustVo.getOrdDate() + "'");
		}
		if (orderCustVo.getOrdCustName() != null && !orderCustVo.getOrdCustName().equals("")) {
			ql.append(" and b.shop_name like '%" + orderCustVo.getOrdCustName() + "%'");
		}
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public PaginationSupport findEveryOrderState(PageController pageController, OrderStateVo orderStateVo) throws GenericException {
		StringBuffer ql = new StringBuffer();

		if (orderStateVo.getOrderState() == 1) {
			ql.append("SELECT u. user_id , u. real_name , u. user_phone FROM t_user u, t_user_role ur, t_role r WHERE u.user_id = ur. user_id AND r. role_id = ur. role_id AND r. role_name = '店长' AND NOT EXISTS (SELECT 1 FROM t_order_custom oc WHERE oc. ord_date ='"
					+ orderStateVo.getOrderDate() + "' AND oc. ord_cust_id = u. user_id )");
		} else if (orderStateVo.getOrderState() == 2) {
			ql.append("SELECT u.user_id, u.real_name, u.user_phone FROM t_user u, t_user_role ur, t_role r WHERE u.user_id = ur.user_id AND r.role_id = ur.role_id AND r.role_name = '供货商' AND EXISTS (SELECT 1 FROM t_order_supplier os WHERE os.ord_date='"
					+ orderStateVo.getOrderDate() + "' AND os.ord_sup_id= u.user_id AND os.ord_state=1)");
		} else if (orderStateVo.getOrderState() == 3) {
			ql.append("SELECT u.user_id, u.real_name, u.user_phone FROM t_user u, t_user_role ur, t_role r WHERE u.user_id = ur.user_id AND r.role_id = ur.role_id AND r.role_name = '店长' AND EXISTS (SELECT 1 FROM t_order_supplier os WHERE os.ord_date = '"
					+ orderStateVo.getOrderDate() + "' AND os.ord_cust_id = u.user_id AND os.ord_state = 2)");
		} else if (orderStateVo.getOrderState() == 4) {
			ql.append("SELECT u.user_id, u.real_name, u.user_phone FROM t_user u, t_user_role ur, t_role r WHERE u.user_id = ur.user_id AND r.role_id = ur.role_id AND r.role_name = '供货商' AND EXISTS (SELECT 1 FROM t_order_supplier os WHERE os.ord_date = '2015-09-22' AND os.ord_sup_id = u.user_id AND os.ord_state = 3)SELECT u.user_id, u.real_name, u.user_phone FROM t_user u, t_user_role ur, t_role r WHERE u.user_id = ur.user_id AND r.role_id = ur.role_id AND r.role_name = '店长' AND EXISTS (SELECT 1 FROM t_order_supplier os WHERE os.ord_date = '"
					+ orderStateVo.getOrderDate() + "' AND os.ord_sup_id = u.user_id AND os.ord_state = 3))");
		} else if (orderStateVo.getOrderState() == 5) {
			ql.append("SELECT u.user_id, u.real_name, u.user_phone FROM t_user u, t_user_role ur, t_role r WHERE u.user_id = ur.user_id AND r.role_id = ur.role_id AND r.role_name = '店长' AND EXISTS (SELECT 1 FROM t_order_supplier os WHERE os.ord_date = '2015-09-22' AND os.ord_sup_id = u.user_id AND os.ord_state = 3)SELECT u.user_id, u.real_name, u.user_phone FROM t_user u, t_user_role ur, t_role r WHERE u.user_id = ur.user_id AND r.role_id = ur.role_id AND r.role_name = '店长' AND EXISTS (SELECT 1 FROM t_order_supplier os WHERE os.ord_date = '"
					+ orderStateVo.getOrderDate() + "' AND os.ord_cust_id = u.user_id AND os.ord_state = 4))");
		}

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public PaginationSupport listAllOrderForpurchase(PageController pageController, OrderCustVo orderCustVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT t.ord_id ordId,t.ord_cust_id ordCustId,t.ord_state ordState,t.ord_date ordDate,t.ord_day ordDay,t.ord_value ordValue,b.shop_name,t.review_remark,t.submit_ord_time_last,t.buy_self_flag  FROM  t_order_custom t,t_shop b where b.shop_id = t.ord_cust_id ");
		if (orderCustVo.getOrdState() != 0) {
			ql.append(" and t.ord_state = '" + orderCustVo.getOrdState() + "'");
		}
		if (orderCustVo.getOrdDate() != null && !orderCustVo.getOrdDate().equals("")) {
			ql.append(" and t.ord_date = '" + orderCustVo.getOrdDate() + "'");
		}
		if (orderCustVo.getOrdCustName() != null && !orderCustVo.getOrdCustName().equals("")) {
			ql.append(" and b.shop_name like '%" + orderCustVo.getOrdCustName() + "%'");
		}
		ql.append(" AND EXISTS (SELECT 1 FROM t_shop s WHERE s.`shop_id` = b.`shop_id` and s.`company_user` = '" + orderCustVo.getCompanyUser()
				+ "')");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public int findInsertId() throws GenericException {
		int quert = this.entityManager.createNativeQuery("SELECT LAST_INSERT_ID()").getFirstResult();
		return quert;
	}

	@Override
	public PaginationSupport reviewOrderList(PageController pageController, OrderCustVo orderCustVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT t.ord_id ordId,t.ord_cust_id ordCustId,t.ord_state ordState,t.ord_date ordDate,t.ord_day ordDay  FROM  t_order_custom t ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public OrderCustPo getOrderCust(String ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		OrderCustPo c = new OrderCustPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderCustPo o where o.ordId=" + ordId);
		List<OrderCustPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	@Override
	public List<OrderCustPo> getOrderCustList(String ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		OrderCustPo c = new OrderCustPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderCustPo o where o.ordId=" + ordId);
		List<OrderCustPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<OrderCustPo> getOrderCustListByOrderDateAndShopId(String orderDate, long shopId, int buySelfFlag) throws GenericException {
		JpaTemplate template = this.getTemplate();
		OrderCustPo c = new OrderCustPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderCustPo o where  o.buySelfFlag = '" + buySelfFlag + "' AND o.ordDate='" + orderDate + "' and o.ordCustId= '" + shopId
				+ "'");
		List<OrderCustPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderSupplierPo> getOrderCustListByOrderDateAndShopIdAndSupId(String orderDate, long shopId, long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		OrderCustPo c = new OrderCustPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where  o.ordSupId = '" + supId + "' AND o.ordDate='" + orderDate + "' and o.ordCustId= '" + shopId + "'");
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<OrderSupplierMaterPo> getOrderSupplierMaterPoByFoodIdAndMateridAndOid(String foodId, String materId, String ordId)
			throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierMaterPo o where o.ordId='" + ordId + "' and o.ordFoodId = '" + foodId + "' and o.ordMaterId = '" + materId
				+ "'");
		List<OrderSupplierMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderSupplierMaterPo> getOrderSupplierMaterPoByMateridOrdId(int materId, int ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierMaterPo o where o.ordId='" + ordId + "' and o.ordMaterId = '" + materId + "'");
		List<OrderSupplierMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderSupplierMaterPo> getOrderSupplierMaterPoByMateridOrdId(int materId, int ordId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierMaterPo o where o.ordSupId = '" + supId + "' AND o.ordId='" + ordId + "' and o.ordMaterId = '" + materId + "'");
		List<OrderSupplierMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderSupplierMaterPo> getOrderSupplierMaterPoByMateridOrdIdSupIdShopId(int materId, int ordId, long supId, long shopId)
			throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select a ");
		ql.append(" from OrderSupplierMaterPo a,OrderSupplierPo b where a.ordSupId=b.ordSupId AND a.ordId=b.ordId AND  a.ordSupId = '" + supId
				+ "' AND a.ordId='" + ordId + "' and a.ordMaterId = '" + materId + "' AND b.ordCustId='" + shopId + "'");
		List<OrderSupplierMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<Object> getOrderSupplierMaterPoByMaterIdAndOrderDateAndSupId(int materId, String date, int supId) throws GenericException {
		List<Object> list = this.entityManager.createNativeQuery(
				" SELECT SUM(a.ord_mater_weight) FROM t_order_supplier_mater a, t_order_supplier b WHERE a.ord_mater_id = '" + materId
						+ "' AND a.ord_id=b.ord_id AND a.ord_sup_id = b.ord_sup_id AND b.ord_date = '" + date + "' AND a.ord_sup_id = '" + supId
						+ "' ").getResultList();

		return list;
	}

	/**
	 * 删单子 校验是否发货
	 * */
	public List<OrderSupplierPo> getOrderSupplierPoByOrdIdForCheck(String ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where o.ordId=" + ordId + " AND o.ordState !='" + CommonType.ORD_SUP_WAIT + "' ");
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	/**
	 * 删单子 校验是否发货
	 * */
	public List<OrderSupplierPo> getOrderSupplierPoByOrdIdAndSupIdForCheck(String ordId, String supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where  o.ordSupId = '" + supId + "' and o.ordId=" + ordId + " AND o.ordState !='"
				+ CommonType.ORD_SUP_WAIT + "' ");
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<OrderSupplierPo> getOrderSupplierPoByOrdIdAndSupId(String ordId, String supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where o.ordId=" + ordId + " and o.ordSupId IN('" + supId + "') ");
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderSupplierPo> getOrderSupplierPoByOrdId(String ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where o.ordId='" + ordId + "'");
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderSupplierPo> getOrderSupplierPoByOrdDateAndSupId(String ordDate, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where o.ordDate='" + ordDate + "' and o.ordSupId = '" + supId + "'");
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<OrderSupplierPo> getOrderSupplierPoByOrdDateAndSupIdAndCustIdAll(String ordDate, int supId, String custIdAll) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where o.ordDate='" + ordDate + "' and o.ordSupId = '" + supId + "' And o.ordCustId in(" + custIdAll + ") ");
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderSupplierPo> getOrderSupplierPoByOrdDateAndSupIdAndShopId(String ordDate, int supId, long shopId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where o.ordDate='" + ordDate + "' and o.ordSupId = '" + supId + "' AND o.ordCustId ='" + shopId + "'");
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<OrderCustFoodPo> getOrderCustFood(String ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		OrderCustFoodPo c = new OrderCustFoodPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderCustFoodPo o where o.ordId=" + ordId);
		List<OrderCustFoodPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<OrderCustFoodPo> getOrderCustFoodByOrdIdMaterIdMaterLevel(int ordId, int materId, int materLevel) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderCustFoodPo o where o.ordId='" + ordId + "' and o.ordMaterId= '" + materId + "' and o.ordMaterLevel='" + materLevel
				+ "'");
		List<OrderCustFoodPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderCustFoodPo> getOrderCustFoodByOrdId(int ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderCustFoodPo o where o.ordId='" + ordId + "'");
		List<OrderCustFoodPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderCustFoodPo> getOrderCustFoodByOrdIdForAuxiliaryMater(int ordId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderCustFoodPo o where o.ordId='" + ordId
				+ "' AND  EXISTS (SELECT 1 FROM  UnitMaterConvertPo u WHERE u.auxiliaryMaterId = o.ordMaterId AND u.supId = '" + supId + "' )");
		List<OrderCustFoodPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public void passOrder(OrderCustPo c) throws GenericException {
		this.merge(c);
	}

	@Override
	public List<FoodMaterPo> getFootMaterByFoodId(int foodId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from FoodMaterPo o where o.foodId=" + foodId);
		List<FoodMaterPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public MaterPo getMaterValueByMaterId(int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterPo o where o.materId=" + materId);
		List<MaterPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	@Override
	public MaterLevelPo getMaterLevelByMaterId(int materId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materLevel = '1' and o.materId=" + materId);
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	public List<MaterLevelPo> checkMaterLevelByUserSup(int materId, int userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT o FROM MaterLevelPo o , MaterPo a, ShopSupplierTypePo b WHERE o.materId = '" + materId
				+ "' AND o.materLevel =1 AND o.materId = a.materId AND b.userId = '" + userId
				+ "' AND b.supType = a.materType AND b.supId = o.supId ");
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public MaterLevelPo getMaterLevelByMaterIdAndLevel(int materId, int level) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from MaterLevelPo o where o.materLevel = '" + level + "' and o.materId=" + materId);
		List<MaterLevelPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	@Override
	public BigDecimal getFootValueByFoodId(int foodId, int userId) throws GenericException {

		List list = this.entityManager
				.createNativeQuery(
						" SELECT SUM( c.mater_value * b.mater_weight * c.mater_proportion * 0.01 ) FROM t_material_level c INNER JOIN t_food_mater b ON b.food_id = '"
								+ foodId
								+ "' AND b.mater_id = c.mater_id AND c.mater_level = 1 LEFT JOIN t_material d ON b.mater_id=d.mater_id INNER JOIN t_user_supplier e ON e.user_id= "
								+ userId + " AND e.sup_type=d.mater_type AND e.sup_id = c.sup_id").getResultList();
		return new BigDecimal(list.get(0).toString());
	}

	@Override
	public ShopSupplierTypePo getSuppCusPoByMaterType(int materType, int shopId) {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from ShopSupplierTypePo o where o.supType=" + materType + "and o.shopId=" + shopId);
		List<ShopSupplierTypePo> list = template.find(ql.toString(), null);
		return list.get(0);

	}

	@Override
	public List<Object[]> getOrderSupplierMaterByOrdId(String ordId) {

		List list = this.entityManager.createNativeQuery(
				" SELECT ord_sup_id ,SUM(ord_mater_value) FROM t_order_supplier_mater  WHERE ord_id =" + ordId + " GROUP BY ord_sup_id")
				.getResultList();

		return list;
	}

	public List<Object[]> getOrderSupplierMaterByOrdIdAndSupIdAndShopId(String ordId, long supId, long shopId) throws GenericException {
		List list = this.entityManager
				.createNativeQuery(
						"SELECT a.ord_sup_id, SUM(a.ord_mater_value) FROM t_order_supplier_mater a ,t_order_supplier b WHERE a.ord_id =b.ord_id AND a.ord_sup_id = b.ord_sup_id AND b.ord_cust_id = '"
								+ shopId + "' AND a.ord_sup_id = '" + supId + "' AND a.ord_id ='" + ordId + "'").getResultList();
		return list;
	}

	public OrderSupplierPo getOrderSupplierPoByOrdId(int id, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderSupplierPo o where o.ordId=" + id + " and o.ordSupId =" + supId);
		List<OrderSupplierPo> list = template.find(ql.toString(), null);
		return list.get(0);
	}

	public void deleteOrder(int ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete OrderCustPo o where o.ordId=" + ordId);
		template.executeUpdate(ql.toString());
		ql = new StringBuffer("");

		ql.append(" delete OrderCustFoodPo o where o.ordId=" + ordId);
		template.executeUpdate(ql.toString());
		ql = new StringBuffer("");

		ql.append(" delete OrderSupplierPo o where o.ordId=" + ordId);
		template.executeUpdate(ql.toString());
		ql = new StringBuffer("");

		ql.append(" delete OrderSupplierMaterPo o where o.ordId=" + ordId);
		template.executeUpdate(ql.toString());
		ql = new StringBuffer("");

		ql.append(" delete OrderSupplierMaterNeedPo o where o.ordId=" + ordId);
		template.executeUpdate(ql.toString());
		ql = new StringBuffer("");

		ql.append(" delete OrderSupplierMaterRealPo o where o.ordId=" + ordId);
		template.executeUpdate(ql.toString());
		ql = new StringBuffer("");
	}

	public void deleteSupOrderByOid(int ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();

		ql.append(" delete OrderSupplierPo o where o.ordId=" + ordId);
		template.executeUpdate(ql.toString());
		ql = new StringBuffer("");

		/*
		 * ql.append(" delete OrderSupplierMaterPo o where o.ordId=" + ordId);
		 * template.executeUpdate(ql.toString()); ql = new StringBuffer("");
		 */

	}

	public void deleteSupOrderByOidSupId(int ordId, int ordSupId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();

		ql.append(" delete OrderSupplierPo o where o.ordId='" + ordId + "' and o.ordSupId='" + ordSupId + "'");
		template.executeUpdate(ql.toString());
		ql = new StringBuffer("");
	}

	@Override
	public void addOrderSupplier(OrderSupplierPo po) throws GenericException {
		this.persist(po);
	}

	@Override
	public void updateOrderSupplier(OrderSupplierPo po) throws GenericException {
		this.merge(po);
	}

	public void updateOrderCustFoodPo(OrderCustFoodPo po) throws GenericException {
		this.merge(po);
	}

	public List<Object[]> getRealValue(int ordId, int Level, String realWeight, int supId, int materId) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT c.mater_value_undressed * 1000 * '" + realWeight
				+ "' FROM t_order_supplier b, t_material_level_bl c WHERE c.mater_day = b.ord_date AND b.ord_sup_id = c.sup_id AND c.mater_id ='"
				+ materId + "' AND c.sup_id = '" + supId + "' AND b.ord_id = '" + ordId + "' AND c.mater_level = '" + Level + "' ");

		List list = this.entityManager.createNativeQuery(ql.toString()).getResultList();

		return list;
	}

	public List<SupplierQualificationPo> getSupplierQualificationPoListByName(String name) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from SupplierQualificationPo o where o.name='" + name + "'");
		List<SupplierQualificationPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderSupplierMaterRealPo> getOrderSupplierMaterRealPoBysupIdMaterDayMaterId(String materDay, int supId, long shopId, int materId)
			throws GenericException {

		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT r FROM OrderSupplierMaterRealPo r, OrderSupplierPo s WHERE s.ordId=r.ordId AND s.ordDate = '" + materDay
				+ "' AND r.ordSupId = '" + supId + "' AND r.ordSupId = s.ordSupId AND s.ordCustId = '" + shopId + "' AND r.ordMaterId='" + materId
				+ "' ");

		List<OrderSupplierMaterRealPo> list = template.find(ql.toString(), null);
		return list;

	}

}
